package manager;

/**
 * @author huangjm.2012, mohameds.2012, jaslin.wee.2012, huiwensong.2012, sheryl.tay.2012
 *
 */

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import manager.database.DatabaseMgr;
import manager.entitycreator.CourseCreator;
import manager.entitycreator.EntityCreator;
import util.DateUtil;
import util.ValidationUtil;
import entity.Bid;
import entity.Course;

public class CourseMgr {

	private DatabaseMgr dbMgr = new DatabaseMgr();
	private CourseCreator courseCreator = new CourseCreator();

	public boolean addCourse(Course course) {

		String sql = "insert into course "
				+ " (course_courseCode, course_school, course_title, course_description, course_examDate, course_examStartTime, course_examEndTime) "
				+ " values (?, ?, ?, ?, ?, ?, ?) ";

		int numRowsAffected = dbMgr.executeUpdate(sql, course.getCourseCode(),
				course.getSchool(), course.getTitle(), course.getDescription(),
				course.getExamDate(DateUtil.MYSQL_DATE_PATTERN),
				course.getExamStartTime(DateUtil.MYSQL_TIME_PATTERN),
				course.getExamEndTime(DateUtil.MYSQL_TIME_PATTERN));

		return numRowsAffected > 0;
	}

	public int[] addMultipleCourses(ArrayList<Course> courses) {
		String sql = "insert into course "
				+ " (course_courseCode, course_school, course_title, course_description, course_examDate, course_examStartTime, course_examEndTime) "
				+ " values (?, ?, ?, ?, ?, ?, ?) ";

		int[] numRowsAffected = { 0 };
		try {
			dbMgr.open();

			dbMgr.setStatement(sql);
			for (Course eachCourse : courses) {
				dbMgr.addBatch(eachCourse.getCourseCode(), eachCourse
						.getSchool(), eachCourse.getTitle(), eachCourse
						.getDescription(), eachCourse
						.getExamDate(DateUtil.MYSQL_DATE_PATTERN), eachCourse
						.getExamStartTime(DateUtil.MYSQL_TIME_PATTERN),
						eachCourse.getExamEndTime(DateUtil.MYSQL_TIME_PATTERN));
			}
			numRowsAffected = dbMgr.executeBatch();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbMgr.close();
		}
		return numRowsAffected;
	}

	public void reset() {
		dbMgr.executeUpdate("delete from course");
	}

	public ArrayList<Course> retrieveAllCourses() {
		return dbMgr.retrieveMultipleEntitiesForQuery(courseCreator,
				"select * from course order by course_courseCode ");
	}

	public ArrayList<String> retrieveAllSchoolsThatHaveCourses() {
		String sql = "select * from schools_that_have_courses order by course_school";
		EntityCreator<String> schoolCreator = new EntityCreator<String>() {
			@Override
			public String newEntityFromResultSet(ResultSet rs)
					throws SQLException {
				return rs.getString("course_school");
			}
		};
		return dbMgr.retrieveMultipleEntitiesForQuery(schoolCreator, sql);
	}

	public Course retrieveCourseForCourseCode(String courseCode) {

		String sql = "select * from course where course_courseCode=?";

		Course course = dbMgr.retrieveEntityForQuery(courseCreator, sql,
				courseCode);

		ValidationUtil.checkForElement(course);

		return course;
	}

	public ArrayList<Course> retrieveCoursesOfferedBySchool(String school) {

		return dbMgr
				.retrieveMultipleEntitiesForQuery(
						courseCreator,
						"select * from course where course_school=? order by course_courseCode",
						school);
	}

}
